create database Student
on
(
	name='Student_data',
	filename='F:\sql\Student_data.mdf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='F:\sql\Student_log.ldf',
	size=5MB,
	maxsize=50MB,
	filegrowth=10%
)
go

use Student
go
create table Class
(
	ClassID int primary key identity(1,1),
	ClassName nvarchar(20)  unique not null 
)

create table Student
(
	StuID int primary key identity(1,1),
	ClassID int references Class(ClassID),
	StuName nvarchar(20) not null,
	StuSex nvarchar(1) default('男') check(StuSex='男' or StuSex='女'),
	StuBirthday date,
	StuPhone nvarchar(11) unique,
)
--加字段
--alter table 表名 add 字段名 数据类型 约束	
--创建学生信息表的时候，不要创建“地址”字段，该字段在创建表后，用语句加上。
alter table Student add StuAddress nvarchar(200)

create table Course
(
	CourseID int primary key identity,
	CourseName nvarchar(50) unique not null,
	CourseCredit int default(1) check(CourseCredit>=1 and CourseCredit<=5) not null,
)

create table Score
(
	ScoreID int identity not null,
	StuID int ,
	CourseID int,
	Score decimal(5,2) unique not null
)

--添加约束
--alter table 表名 add constraint 约束名 约束的类型 
alter table Score add constraint PK_Score_ScoreID primary key (ScoreID)
alter table Score add constraint FK_Score_StuID foreign key(StuID) references Student(StuID)
alter table Score add constraint FK_Score_CourseID foreign key(CourseID) references Course(CourseID)